{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "SystemExit",
     "evalue": "0",
     "output_type": "error",
     "traceback": [
      "An exception has occurred, use %tb to see the full traceback.\n",
      "\u001b[1;31mSystemExit\u001b[0m\u001b[1;31m:\u001b[0m 0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\software\\miniconda3\\envs\\py311\\Lib\\site-packages\\IPython\\core\\interactiveshell.py:3585: UserWarning: To exit: use 'exit', 'quit', or Ctrl-D.\n",
      "  warn(\"To exit: use 'exit', 'quit', or Ctrl-D.\", stacklevel=1)\n"
     ]
    }
   ],
   "source": [
    "# 查询1 可视化\n",
    "import sys\n",
    "import pymysql\n",
    "from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit\n",
    "import json\n",
    "\n",
    "class QueryWindow(QWidget):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "\n",
    "        self.init_ui()\n",
    "\n",
    "    def init_ui(self):\n",
    "        # 设置窗口标题和大小\n",
    "        self.setWindowTitle('MySQL Items Query')\n",
    "        self.setGeometry(100, 100, 500, 300)\n",
    "\n",
    "        layout = QVBoxLayout()\n",
    "\n",
    "        # 创建提示标签\n",
    "        id_label = QLabel('请输入要查询的实体名:')\n",
    "        layout.addWidget(id_label)\n",
    "\n",
    "        # 创建输入框\n",
    "        self.id_edit = QLineEdit()\n",
    "        layout.addWidget(self.id_edit)\n",
    "\n",
    "        # 创建查询按钮\n",
    "        query_button = QPushButton('查询')\n",
    "        query_button.clicked.connect(self.query_data)\n",
    "        layout.addWidget(query_button)\n",
    "\n",
    "        # 创建用于显示结果的文本框\n",
    "        self.result_text = QTextEdit()\n",
    "        self.result_text.setReadOnly(True)\n",
    "        layout.addWidget(self.result_text)\n",
    "\n",
    "        self.setLayout(layout)\n",
    "\n",
    "    def query_data(self):\n",
    "        input_id = self.id_edit.text()\n",
    "        if not input_id:\n",
    "            self.result_text.setText(\"请输入有效的实体名\")\n",
    "            return\n",
    "\n",
    "        try:\n",
    "            # 连接MySQL数据库，根据实际情况修改配置参数\n",
    "            mydb = pymysql.connect(\n",
    "                host=\"localhost\",\n",
    "                user=\"root\",\n",
    "                password=\"123456\",\n",
    "                database=\"wikidata\"\n",
    "            )\n",
    "            mycursor = mydb.cursor()\n",
    "\n",
    "            # 查询语句，根据id查询items表中的所有字段\n",
    "            query = f\"\"\"SELECT * FROM items\n",
    "    WHERE JSON_SEARCH(labels, 'one', '%{input_id}%') IS NOT NULL\n",
    "    OR JSON_SEARCH(aliases, 'one', '%{input_id}%') IS NOT NULL;\"\"\"\n",
    "            mycursor.execute(query)\n",
    "            result = mycursor.fetchall()\n",
    "\n",
    "            if result:\n",
    "                # print(result)\n",
    "                # 将查询结果转换为便于阅读的字符串格式\n",
    "                result_str = f'共有 {len(result)} 条记录\\n'\n",
    "                for item in result:\n",
    "                    # print(item)\n",
    "                    result_str += f\"ID: {item[1]}\\n\"\n",
    "                    label = json.loads(item[2])\n",
    "                    result_str += f\"Label(zh): {label['zh']['value']}\\n\"\n",
    "                    aliases = json.loads(item[4])\n",
    "                    result_str += f\"Aliases(zh):\"\n",
    "                    if aliases.get('zh', None) is not None:\n",
    "                        for alias in aliases['zh']:\n",
    "                            result_str += f\" {alias['value']},\"\n",
    "                        result_str = result_str.rstrip(',')\n",
    "                    result_str += \"\\n\"\n",
    "                    descriptions = json.loads(item[3])\n",
    "                    result_str += f\"Descriptions(zh): {descriptions['zh']['value']}\\n\"\n",
    "                    # result_str += f\"Descriptions(zh): {item[3]['zh']['value']}\\n\"\n",
    "                    result_str += f\"************************************\\n\"\n",
    "                self.result_text.setText(result_str)\n",
    "            else:\n",
    "                self.result_text.setText(f\"未找到实体名为 {input_id} 的记录\")\n",
    "\n",
    "            mycursor.close()\n",
    "            mydb.close()\n",
    "        except Exception as err:\n",
    "            self.result_text.setText(f\"查询出错: {err}\")\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app = QApplication(sys.argv)\n",
    "    window = QueryWindow()\n",
    "    window.show()\n",
    "    sys.exit(app.exec_())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'value': ['Q3624078', 'Q43702', 'Q6256', 'Q20181813', 'Q1250464']}\n"
     ]
    }
   ],
   "source": [
    "# 查询2\n",
    "import sys\n",
    "import pymysql\n",
    "from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit\n",
    "import json\n",
    "\n",
    "class QueryWindow(QWidget):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "\n",
    "        self.init_ui()\n",
    "\n",
    "    def init_ui(self):\n",
    "        # 设置窗口标题和大小\n",
    "        self.setWindowTitle('MySQL 查询2')\n",
    "        self.setGeometry(100, 100, 500, 300)\n",
    "\n",
    "        layout = QVBoxLayout()\n",
    "\n",
    "        # 创建提示标签\n",
    "        id_label = QLabel('请输入要查询的实体ID:')\n",
    "        layout.addWidget(id_label)\n",
    "\n",
    "        # 创建输入框\n",
    "        self.id_edit = QLineEdit()\n",
    "        layout.addWidget(self.id_edit)\n",
    "\n",
    "        # 创建查询按钮\n",
    "        query_button = QPushButton('查询')\n",
    "        query_button.clicked.connect(self.query_data)\n",
    "        layout.addWidget(query_button)\n",
    "\n",
    "        # 创建用于显示结果的文本框\n",
    "        self.result_text = QTextEdit()\n",
    "        self.result_text.setReadOnly(True)\n",
    "        layout.addWidget(self.result_text)\n",
    "\n",
    "        self.setLayout(layout)\n",
    "\n",
    "    def query_data(self):\n",
    "        input_id = self.id_edit.text()\n",
    "        if not input_id:\n",
    "            self.result_text.setText(\"请输入有效的实体ID\")\n",
    "            return\n",
    "\n",
    "        try:\n",
    "            # 连接MySQL数据库，根据实际情况修改配置参数\n",
    "            mydb = pymysql.connect(\n",
    "                host=\"localhost\",\n",
    "                user=\"root\",\n",
    "                password=\"123456\",\n",
    "                database=\"wikidata\"\n",
    "            )\n",
    "            mycursor = mydb.cursor()\n",
    "\n",
    "            # 查询语句，根据id查询items表中的所有字段\n",
    "            query = f\"\"\"select qid_list\n",
    "from query2\n",
    "where qid='{input_id}';\"\"\"\n",
    "            mycursor.execute(query)\n",
    "            result = mycursor.fetchall()\n",
    "\n",
    "            if result:\n",
    "                # print(result[0])\n",
    "                # 将查询结果转换为便于阅读的字符串格式\n",
    "                result_str = f'{input_id}的类或父类有：'\n",
    "                item=json.loads(result[0][0])\n",
    "                # print(item)\n",
    "                for v in item['value']:\n",
    "                    result_str += f'\\n{v}'\n",
    "                self.result_text.setText(result_str)\n",
    "            else:\n",
    "                self.result_text.setText(f\"未找到实体名为 {input_id} 的记录\")\n",
    "\n",
    "            mycursor.close()\n",
    "            mydb.close()\n",
    "        except Exception as err:\n",
    "            self.result_text.setText(f\"查询出错: {err}\")\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app = QApplication(sys.argv)\n",
    "    window = QueryWindow()\n",
    "    window.show()\n",
    "    sys.exit(app.exec_())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询3\n",
    "\n",
    "import sys\n",
    "import pymysql\n",
    "from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit\n",
    "import json\n",
    "\n",
    "class QueryWindow(QWidget):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "\n",
    "        self.init_ui()\n",
    "\n",
    "    def init_ui(self):\n",
    "        # 设置窗口标题和大小\n",
    "        self.setWindowTitle('MySQL 查询3')\n",
    "        self.setGeometry(100, 100, 500, 300)\n",
    "\n",
    "        layout = QVBoxLayout()\n",
    "\n",
    "        # 创建提示标签\n",
    "        id_label = QLabel('请输入要查询的实体ID:')\n",
    "        layout.addWidget(id_label)\n",
    "\n",
    "        # 创建输入框\n",
    "        self.id_edit = QLineEdit()\n",
    "        layout.addWidget(self.id_edit)\n",
    "\n",
    "        # 创建查询按钮\n",
    "        query_button = QPushButton('查询')\n",
    "        query_button.clicked.connect(self.query_data)\n",
    "        layout.addWidget(query_button)\n",
    "\n",
    "        # 创建用于显示结果的文本框\n",
    "        self.result_text = QTextEdit()\n",
    "        self.result_text.setReadOnly(True)\n",
    "        layout.addWidget(self.result_text)\n",
    "\n",
    "        self.setLayout(layout)\n",
    "\n",
    "    def query_data(self):\n",
    "        input_id = self.id_edit.text()\n",
    "        if not input_id:\n",
    "            self.result_text.setText(\"请输入有效的实体ID\")\n",
    "            return\n",
    "\n",
    "        try:\n",
    "            # 连接MySQL数据库，根据实际情况修改配置参数\n",
    "            mydb = pymysql.connect(\n",
    "                host=\"localhost\",\n",
    "                user=\"root\",\n",
    "                password=\"123456\",\n",
    "                database=\"wikidata\"\n",
    "            )\n",
    "            mycursor = mydb.cursor()\n",
    "\n",
    "            # 查询语句，根据id查询items表中的所有字段\n",
    "            query = f\"\"\"select qid_list\n",
    "from query3\n",
    "where qid='{input_id}';\"\"\"\n",
    "            mycursor.execute(query)\n",
    "            result = mycursor.fetchall()\n",
    "\n",
    "            if result:\n",
    "                # print(result[0])\n",
    "                # 将查询结果转换为便于阅读的字符串格式\n",
    "                result_str = f'与{input_id}出现在同一陈述的实体'\n",
    "                item=json.loads(result[0][0])\n",
    "                result_str += f'共有{len(item)}个'\n",
    "                for v in item:\n",
    "                    if v != input_id:\n",
    "                        result_str += f'\\n{v}'\n",
    "                self.result_text.setText(result_str)\n",
    "            else:\n",
    "                self.result_text.setText(f\"未找到实体ID为 {input_id} 的记录\")\n",
    "\n",
    "            mycursor.close()\n",
    "            mydb.close()\n",
    "        except Exception as err:\n",
    "            self.result_text.setText(f\"查询出错: {err}\")\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app = QApplication(sys.argv)\n",
    "    window = QueryWindow()\n",
    "    window.show()\n",
    "    sys.exit(app.exec_())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询4\n",
    "\n",
    "import sys\n",
    "import pymysql\n",
    "from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit\n",
    "import json\n",
    "\n",
    "class QueryWindow(QWidget):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "\n",
    "        self.init_ui()\n",
    "\n",
    "    def init_ui(self):\n",
    "        # 设置窗口标题和大小\n",
    "        self.setWindowTitle('MySQL 查询4')\n",
    "        self.setGeometry(100, 100, 500, 300)\n",
    "\n",
    "        layout = QVBoxLayout()\n",
    "\n",
    "        # 创建提示标签\n",
    "        id_label = QLabel('请输入要查询的实体ID:')\n",
    "        layout.addWidget(id_label)\n",
    "\n",
    "        # 创建输入框\n",
    "        self.id_edit = QLineEdit()\n",
    "        layout.addWidget(self.id_edit)\n",
    "\n",
    "        # 创建查询按钮\n",
    "        query_button = QPushButton('查询')\n",
    "        query_button.clicked.connect(self.query_data)\n",
    "        layout.addWidget(query_button)\n",
    "\n",
    "        # 创建用于显示结果的文本框\n",
    "        self.result_text = QTextEdit()\n",
    "        self.result_text.setReadOnly(True)\n",
    "        layout.addWidget(self.result_text)\n",
    "\n",
    "        self.setLayout(layout)\n",
    "\n",
    "    def query_data(self):\n",
    "        input_id = self.id_edit.text()\n",
    "        if not input_id:\n",
    "            self.result_text.setText(\"请输入有效的实体ID\")\n",
    "            return\n",
    "\n",
    "        try:\n",
    "            # 连接MySQL数据库，根据实际情况修改配置参数\n",
    "            mydb = pymysql.connect(\n",
    "                host=\"localhost\",\n",
    "                user=\"root\",\n",
    "                password=\"123456\",\n",
    "                database=\"wikidata\"\n",
    "            )\n",
    "            mycursor = mydb.cursor()\n",
    "\n",
    "            # 查询语句，根据id查询items表中的所有字段\n",
    "            query = f\"\"\"select *\n",
    "from item_pp\n",
    "where qid='{input_id}' and rank1!='deprecated';\"\"\"\n",
    "            mycursor.execute(query)\n",
    "            result = mycursor.fetchall()\n",
    "\n",
    "            if result:\n",
    "                result_str = f'{input_id}的属性值列表为：\\n'\n",
    "                for row in result:\n",
    "                    pid = row[1]\n",
    "                    mainsnak = json.loads(row[2])\n",
    "                    if row[4]:\n",
    "                        qualifiers = json.loads(row[4])\n",
    "                    if row[-1]:\n",
    "                        ref = json.loads(row[-1])\n",
    "                    result_str += f'属性ID: {pid}\\n'\n",
    "                    result_str += f'主值: {mainsnak.get(\"datavalue\")}\\n'\n",
    "                    result_str += f'限定值: \\n'\n",
    "                    if row[4]:\n",
    "                        for k,v in qualifiers.items():\n",
    "                            result_str += f'  {k}:{v[0].get(\"datavalue\")}\\n'\n",
    "                    result_str += '引用:\\n'\n",
    "                    if row[-1]:\n",
    "                        for l in ref['value']:\n",
    "                            for k,v in l['snaks'].items():\n",
    "                                result_str += f'  {k}:{v[0].get(\"datavalue\")}\\n'\n",
    "                    result_str += '**************************************\\n'\n",
    "                self.result_text.setText(result_str)\n",
    "            else:\n",
    "                self.result_text.setText(f\"未找到实体ID为 {input_id} 的记录\")\n",
    "\n",
    "            mycursor.close()\n",
    "            mydb.close()\n",
    "        except Exception as err:\n",
    "            self.result_text.setText(f\"查询出错: {err}\")\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app = QApplication(sys.argv)\n",
    "    window = QueryWindow()\n",
    "    window.show()\n",
    "    sys.exit(app.exec_())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查询5\n",
    "from sentence_transformers import SentenceTransformer\n",
    "import pickle\n",
    "import torch\n",
    "\n",
    "\n",
    "with open(\"query5.pkl\", \"rb\") as f:\n",
    "    query5_list = pickle.load(f)\n",
    "query5_list=query5_list[:10]\n",
    "sentences = [\"This is an example sentence\", \"Each sentence is converted\"]\n",
    "\n",
    "model = SentenceTransformer('sentence-transformers/all-MiniLM-L6-v2')\n",
    "\n",
    "# 查询4\n",
    "\n",
    "import sys\n",
    "# import pymysql\n",
    "from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit\n",
    "import json\n",
    "\n",
    "class QueryWindow(QWidget):\n",
    "    def __init__(self):\n",
    "        super().__init__()\n",
    "\n",
    "        self.init_ui()\n",
    "\n",
    "    def init_ui(self):\n",
    "        # 设置窗口标题和大小\n",
    "        self.setWindowTitle('查询5')\n",
    "        self.setGeometry(100, 100, 500, 300)\n",
    "\n",
    "        layout = QVBoxLayout()\n",
    "\n",
    "        # 创建提示标签\n",
    "        id_label = QLabel('请输入查询语句:')\n",
    "        layout.addWidget(id_label)\n",
    "\n",
    "        # 创建输入框\n",
    "        self.id_edit = QLineEdit()\n",
    "        layout.addWidget(self.id_edit)\n",
    "\n",
    "        # 创建查询按钮\n",
    "        query_button = QPushButton('查询')\n",
    "        query_button.clicked.connect(self.query_data)\n",
    "        layout.addWidget(query_button)\n",
    "\n",
    "        # 创建用于显示结果的文本框\n",
    "        self.result_text = QTextEdit()\n",
    "        self.result_text.setReadOnly(True)\n",
    "        layout.addWidget(self.result_text)\n",
    "\n",
    "        self.setLayout(layout)\n",
    "\n",
    "    def query_data(self):\n",
    "        input_id = self.id_edit.text()\n",
    "        if not input_id:\n",
    "            self.result_text.setText(\"请输入查询语句\")\n",
    "            return\n",
    "        try:\n",
    "            q='what is the population of Kampen?'\n",
    "            q=input_id\n",
    "            res=[]\n",
    "            with torch.no_grad():\n",
    "                q_embed = model.encode(q)\n",
    "            for q in query5_list:\n",
    "                #计算q_embed和q['embed']的余弦相似度\n",
    "                cos_sim = torch.nn.functional.cosine_similarity(torch.tensor(q_embed), torch.tensor(q['embed']), dim=0)\n",
    "                item=q.copy()\n",
    "                #item删除embed字段\n",
    "                del item['embed']\n",
    "                item['score']=cos_sim.item()\n",
    "                res.append(item)\n",
    "            res.sort(key=lambda x:x['score'],reverse=True)\n",
    "            self.result_text.setText(f\"前5条记录\\n{res[0]}\\n{res[1]}\\n{res[2]}\\n{res[3]}\\n{res[4]}\")\n",
    "            \n",
    "        except Exception as err:\n",
    "            self.result_text.setText(f\"查询出错: {err}\")\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app = QApplication(sys.argv)\n",
    "    window = QueryWindow()\n",
    "    window.show()\n",
    "    sys.exit(app.exec_())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py311",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
